package org.transfer.service;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.transfer.model.ImportLog;
import org.transfer.utils.ConfigUtil;
import org.transfer.utils.LogUtils;
import org.transfer.utils.SqlServerUtil;

@Component
public class OriginalSqlServerService {

	// 日志文件存放目录
	private String logPath = "";

	// 恢复文件存放目录
	private String recoveryPath = "";

	// 表空间文件存放目录
	private String dataBasePath = "";

	private List<File> logList = new ArrayList<File>();

	// sqlserver 恢复bak文件的成功标志
	private String baksuccess = "成功处理";

	private String userName = "";
	private String passWord = "";
	private String ip = "";
	private String port = "";

	private String DBName = "";

	@Autowired
	private ImportLogService importLogService;

	private String recoveryLogPath = "";

	/**
	 * 执行恢复
	 * 
	 * @throws IOException
	 */
	public void recoveryData() throws IOException {
		System.out.println("===================SqlServer数据恢复开始===================");
		dataBasePath = ConfigUtil.getConfigure().getProperty("dataBasePath");
		logPath = ConfigUtil.getConfigure().getProperty("bakLogsFile");
		recoveryPath = ConfigUtil.getConfigure().getProperty("recoveryPath");
		scanningFile(logList, logPath);
		ip = ConfigUtil.getConfigure().getProperty("hostname");
		userName = ConfigUtil.getConfigure().getProperty("username");
		passWord = ConfigUtil.getConfigure().getProperty("password");
		port = ConfigUtil.getConfigure().getProperty("DBPort");
		File logFile = new File(logPath);
		if (!logFile.exists()) {
			logFile.mkdirs();
		}
		File dataFile = new File(dataBasePath);
		if (!dataFile.exists()) {
			dataFile.mkdirs();
		}
		recoveryLogPath = logPath + "recovery.log";
		List<File> list = new ArrayList<File>();
		scanningFile(list, recoveryPath);
		for (File file : list) {
			String fileName = file.getName().toUpperCase();
			if (fileName.indexOf("BAK") != -1 && fileName.indexOf(".") != -1
					&& "BAK".equals(fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()))) {
				// 执行恢复方法
				boolean result = false;
				ImportLog entity = importLogService
						.get(new ImportLog(file.getName().substring(0, file.getName().indexOf("."))));
				if (entity != null) {
					entity.setBakType("2");
				}
				try {
					result = recoverySqlServer(file);
					if (result) {
						System.out.println("===================" + DBName + "数据恢复===================");
						LogUtils.logs(recoveryLogPath, "【" + file.getName() + "】-【" + DBName + "】恢复成功");
						if (entity != null) {
							entity.setRecoveryState("1");
							entity.setRecoveryMsg("恢复成功");
							importLogService.save(entity);
						}
					} else {
						LogUtils.logs(recoveryLogPath, "【" + file.getName() + "】恢复失败");
						if (entity != null) {
							entity.setRecoveryState("0");
							entity.setRecoveryMsg("恢复失败");
							importLogService.save(entity);
						}
					}
				} catch (Exception e) {
					LogUtils.logs(recoveryLogPath, "【" + file.getName() + "】恢复失败");
					if (entity != null) {
						entity.setRecoveryState("0");
						entity.setRecoveryMsg("恢复失败");
						importLogService.save(entity);
					}
				}
			} else {
				LogUtils.logs(recoveryLogPath, "【" + file.getName() + "】无需恢复");
			}
		}
		System.out.println("===================SqlServer数据恢复结束===================");
	}

	/**
	 * 执行恢复
	 * 
	 * @param filePath
	 */
	public boolean recoverySqlServer(File file) {
		// 修改文件权限(完全控制)
		AccessPermission(file.getParent());
		// 恢复数据库方法
		boolean result = RestoreDatabase(file.getPath(), file.getName());
		return result;
	}

	/**
	 * 数据授权
	 * 
	 * @param filePath
	 */
	public void AccessPermission(String filePath) {
		Process process = null;
		try {
			// 把当前文件的完全控制权限授权所有人
			// 获取管理员权限
			process = SqlServerUtil.cmd("net localgroup administrators " + System.getProperty("user.name") + " /add");
			// 执行命令
			process = SqlServerUtil
					.cmd("cacls " + filePath.substring(0, filePath.length() - 1) + " /T /E /C /G everyone:F");
			// 获取管理员权限
			process = SqlServerUtil
					.cmd("net localgroup administrators " + System.getProperty("user.name") + " /delete");
		} catch (Exception e) {
			throw new RuntimeException("获取恢复文件完全访问权限失败!");
		} finally {
			if (process != null) {
				process.destroy();
			}
			try {
				process.getErrorStream().close();
				process.getInputStream().close();
				process.getOutputStream().close();
			} catch (Exception ee) {
				throw new RuntimeException("关闭cmd工具流失败!");
			}
		}
	}

	/**
	 * 恢复方法
	 * 
	 * @param filePath
	 * @param dataSource
	 * @return
	 */
	public boolean RestoreDatabase(String filePath, String fileName) {

		// 用来存放mdf格式文件的逻辑名
		List<String> mdfName = new ArrayList<String>();
		// 用来存放ndf格式文件的逻辑名
		List<String> ndfName = new ArrayList<String>();
		// 用来存放ldf格式文件的逻辑名
		List<String> ldfName = new ArrayList<String>();

		Map<String, List<String>> logicalName = getLogicalName(filePath);
		mdfName = logicalName.get("mdf");
		ndfName = logicalName.get("ndf");
		ldfName = logicalName.get("ldf");
		String db_name = "";
		String newDbName = "";
		try {
			if (mdfName.size() != 0) {
				db_name = mdfName.get(0);
				newDbName = "db_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
				ImportLog entity = importLogService.get(new ImportLog(fileName.substring(0, fileName.indexOf("."))));
				if (entity == null) {
					throw new RuntimeException("恢复失败");
				}
				if ("1".equals(entity.getRecoveryState())) {
					DBName = entity.getDbName();
					return true;
				}
				if (StringUtils.isBlank(entity.getDbName()) || StringUtils.isBlank(entity.getNewDbName())) {
					entity.setDbName(db_name);
					entity.setNewDbName(newDbName);
					importLogService.save(entity);
				} else {
					newDbName = entity.getNewDbName();
				}
				DBName = newDbName;
				/*
				 * for (File file : logList) { if
				 * (file.getName().indexOf(db_name) != -1) { return true; } }
				 */
			} else {
				throw new RuntimeException();
			}
		} catch (Exception e) {
			throw new RuntimeException("获取bak文件逻辑名失败!");
		}
		if (fileName.indexOf(".") != -1) {
			fileName = fileName.substring(0, fileName.indexOf("."));
		}
		// 要执行的cmd命令
		StringBuffer commandf = new StringBuffer();
		BufferedWriter bufferedWriter = null;
		FileWriter fileWriter = null;
		// sql文件的地址
		StringBuffer sqlFilePath = new StringBuffer(logPath + newDbName + ".sql");
		// log文件的地址
		StringBuffer logFilePath = new StringBuffer(logPath + newDbName + ".log");
		// 拼接字符串
		StringBuffer temp = new StringBuffer();
		// 存放日志
		StringBuffer logStr = new StringBuffer();
		try {
			File file = new File(sqlFilePath.toString());
			fileWriter = new FileWriter(file);
			bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false), "GB18030"));
			// 拼接需要执行的语句
			temp.append("USE master RESTORE DATABASE " + newDbName + " FROM DISK = '" + filePath + "' WITH ");
			for (String m : mdfName) {
				temp.append("MOVE '" + m + "' TO '" + dataBasePath + newDbName + ".mdf',");
			}
			for (String n : ndfName) {
				temp.append("MOVE '" + n + "' TO '" + dataBasePath + newDbName + ".ndf',");
			}
			for (String l : ldfName) {
				temp.append("MOVE '" + l + "' TO '" + dataBasePath + newDbName + "_log" + ".ldf',");
			}
			temp.substring(0, temp.length() - 2);
			temp.append(" STATS = 10, REPLACE " + System.lineSeparator());
			temp.append("               go");
			bufferedWriter.write(temp.toString());
			fileWriter.flush();
			bufferedWriter.flush();
		} catch (Exception e) {
			String name = "生成bak文件恢复所需的sql语句文件失败!";
			throw new RuntimeException(name);
		} finally {
			try {
				bufferedWriter.flush();
				fileWriter.flush();
				if (bufferedWriter != null) {
					bufferedWriter.close();
				}
				if (fileWriter != null) {
					fileWriter.close();
				}
			} catch (Exception e) {
				String name = "关闭读写文件所需的io流时失败!";
				throw new RuntimeException(name);
			}
		}
		commandf.append("osql -S " + ip + "," + port + " -U " + userName + " -P " + passWord + "  -i " + sqlFilePath);
		// 执行命令
		Process process = SqlServerUtil.cmd(commandf.toString());
		try {
			// 输出日志文件
			File file = new File(logFilePath.toString());
			fileWriter = new FileWriter(file);
			fileWriter = new FileWriter(logFilePath.toString());
			bufferedWriter = new BufferedWriter(fileWriter);
			// 从工具类中获取日志信息
			InputStreamReader isr = new InputStreamReader(process.getInputStream(), "GB18030");
			BufferedReader br = new BufferedReader(isr);
			String line = null;
			while ((line = br.readLine()) != null) {
				bufferedWriter.write(line + System.lineSeparator());
				logStr.append(line);
			}
			temp.delete(0, temp.length() - 1);
			br.close();
			isr.close();
		} catch (Exception ioe) {
			throw new RuntimeException("读取bak文件恢复日志失败!");
		} finally {
			try {
				bufferedWriter.flush();
				fileWriter.flush();
				if (bufferedWriter != null) {
					bufferedWriter.close();
				}
				if (process != null) {
					process.destroy();
				}
				if (fileWriter != null) {
					fileWriter.close();
				}
			} catch (Exception e) {
				String name = "关闭读文件所需的io流时失败!";
				throw new RuntimeException(name);
			}
			if (process != null) {
				process.destroy();
			}
			try {
				process.getErrorStream().close();
				process.getInputStream().close();
				process.getOutputStream().close();
			} catch (Exception ee) {
				String name = "关闭cmd工具流失败!";
				throw new RuntimeException(name);
			}
		}
		boolean result = false;
		// 判断是否成功
		if (logStr.toString().contains(baksuccess)) {
			deleteFile(sqlFilePath.toString());
			// 没有错误日志则恢复成功
			/*
			 * FileWriter fw; try { // 成功后记录恢复文件信息 String dbLogName =
			 * getDbLogName(fileName, db_name); fw = new FileWriter(logPath +
			 * dbLogName + ".log"); fw.write("恢复成功"); fw.close(); } catch
			 * (IOException e) { e.printStackTrace(); }
			 */
			result = true;
		} else {
			result = false;
			String name = "恢复数据库失败!";
			throw new RuntimeException(name);
		}
		return result;
	}

	/**
	 * 获取bak文件中 mdf ndf ldf 的逻辑文件名
	 * 
	 * @param filePath
	 * @param dataSource
	 * @return
	 */
	public Map<String, List<String>> getLogicalName(String filePath) {
		// 生成sql的路径
		StringBuffer sqlFilePath = new StringBuffer(logPath + "getLogicalName.sql");
		// 要执行的cmd命令
		StringBuffer commandf = new StringBuffer();
		FileWriter fw = null;
		// 临时存放日志文件用于分析
		StringBuilder stringBuilder = new StringBuilder();
		BufferedWriter bw = null;
		try {
			// 创建sql文件用于存放执行的sql语句
			File file = new File(sqlFilePath.toString());
			fw = new FileWriter(file);
			bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false), "GB18030"));
			bw.write("USE master\n" + "go\n" + "\n" + "RESTORE FILELISTONLY\n" + "\n" + "   FROM DISK = '" + filePath
					+ "' \n" + "\n" + "Go");

			commandf.append(
					"osql -S " + ip + "," + port + " -U " + userName + " -P " + passWord + "  -i " + sqlFilePath);
		} catch (Exception e) {
			e.printStackTrace();
			String name = "生成查询bak文件逻辑名的sql语句文件失败!";
			throw new RuntimeException(name);
		} finally {
			try {
				if (bw != null) {
					bw.flush();
				}
				if (fw != null) {
					fw.flush();
				}
				if (bw != null) {
					bw.close();
				}
				if (fw != null) {
					fw.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
				String name = "关闭读写文件所需的io流时失败!";
				throw new RuntimeException(name);
			}
		}
		// 存放处理后的字符串
		String tempString = "";
		// 用来存放mdf格式文件的逻辑名
		List<String> mdfName = new ArrayList<String>();
		// 用来存放ndf格式文件的逻辑名
		List<String> ndfName = new ArrayList<String>();
		// 用来存放ldf格式文件的逻辑名
		List<String> ldfName = new ArrayList<String>();
		Process process = SqlServerUtil.cmd(commandf.toString());
		// 用来存放上述文件名的集合
		Map<String, List<String>> map = new HashMap<String, List<String>>();

		try {
			// 从工具类中获取日志信息
			InputStreamReader isr = new InputStreamReader(process.getInputStream(), "GB18030");
			BufferedReader br = new BufferedReader(isr);
			String line = null;
			while ((line = br.readLine()) != null) {
				stringBuilder.append(line + System.lineSeparator());
			}
			// 解决日志文件格式混乱
			tempString = stringBuilder.toString().replaceAll("\\r\\n\\t", " ");
			ByteArrayInputStream bin = new ByteArrayInputStream(tempString.getBytes());
			br = new BufferedReader(new InputStreamReader(bin));
			String line1 = null;
			while ((line1 = br.readLine()) != null) {
				// 拿取各行匹配到的字符串
				Pattern pattern = Pattern.compile("^\\s\\b(.*?)\\b\\s");
				Matcher matcher = pattern.matcher(line1);
				String type = "";
				while (matcher.find()) {
					String[] str = line1.split("\\s+");
					if (str.length > 3) {
						for (String s : str) {
							if (s.indexOf(".mdf") != -1 || "D".equals(str[3])) {
								mdfName.add(str[1]);
								break;
							}
							if (s.indexOf(".ndf") != -1 || "N".equals(str[3])) {
								ndfName.add(str[1]);
								break;
							}
							if (s.indexOf(".ldf") != -1 || "L".equals(str[3])) {
								ldfName.add(str[1]);
								break;
							}
						}
					}
				}
			}

			map.put("mdf", mdfName);
			map.put("ndf", ndfName);
			map.put("ldf", ldfName);
			br.close();
			isr.close();
		} catch (Exception ioe) {
			String name = "读取查询bak文件中逻辑名的日志文件失败!";
			throw new RuntimeException(name);
		} finally {
			if (process != null) {
				process.destroy();
			}
			try {
				process.getErrorStream().close();
				process.getInputStream().close();
				process.getOutputStream().close();
			} catch (Exception ee) {
				String name = "关闭cmd工具流失败!";
				throw new RuntimeException(name);
			}
		}
		// 删除生成的sql文件
		deleteFile(sqlFilePath.toString());
		return map;
	}

	/**
	 * 删除恢复过程中产生的文件
	 *
	 * @param Path
	 */
	public boolean deleteFile(String Path) {
		boolean isTrue = false;
		try {
			File file = new File(Path);
			isTrue = file.delete();
		} catch (Exception e) {
			String name = "删除恢复过程中生成的临时文件失败!";
			throw new RuntimeException(name);
		}
		return isTrue;
	}

	/**
	 * 检查目录下所有文件
	 * 
	 * @param list
	 * @param filePath
	 * @return
	 */
	public List<File> scanningFile(List<File> list, String filePath) {
		File document = new File(filePath);
		if (!document.exists()) {
			document.mkdirs();
		}
		if (!document.isFile()) {
			File[] files = document.listFiles();
			for (File file : files) {
				scanningFile(list, file.getPath());
			}
		} else {
			list.add(document);
		}
		return list;
	}

}
